package com.travelms.dao;


import com.travelms.pojo.*;

import javax.security.auth.login.AccountNotFoundException;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

//此类对数据库简单的查询操作
public class DBFind {
    public Connection connection;
    PreparedStatement preparedStatement;
    ResultSet resultSet;
    public DBFind() throws IOException, SQLException {
        Properties properties = new Properties();

        properties.load(new FileInputStream("docs/application.properties"));

        String url = properties.getProperty("url");
        String username = properties.getProperty("username");
        String password = properties.getProperty("password");
        connection = DriverManager.getConnection(url,username,password);

    }
    public  boolean isContainAccount(String account){ //账号查询
        return true;
    }

    //查询数据库中是否有该账户密码，普通用户效验
    public  boolean isContainAccountAndPassword(String ac, String pw) throws SQLException {
        String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,ac);
        preparedStatement.setString(2,pw);
        preparedStatement.execute();
        return preparedStatement.getResultSet().next();
    }

    public  String getUserData(String account){//获取用户所有信息
        return "";
    }

    public boolean registerUser(String user, String password, String key) throws SQLException { //注册写入数据
        String sql = "INSERT INTO users (username, password,secret_key,money) VALUES (?,?,?,?);";
        preparedStatement = connection.prepareStatement(sql);
        int i;
        try {
            preparedStatement.setString(1,user);
            preparedStatement.setString(2,password);
            preparedStatement.setString(3,key);
            preparedStatement.setString(4,"1000");

            i = preparedStatement.executeUpdate();

        } catch (SQLException e) {
            return false;
        }
        return i > 0;

    }
    //管理员用户效验
    public boolean isContainAdmin(String user, String password) throws SQLException {
        String sql = "SELECT * FROM admin WHERE user = ? AND password = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,user);
        preparedStatement.setString(2,password);
        preparedStatement.execute();
        return preparedStatement.getResultSet().next();
    }

    //下面这个方法判断用户是否重复登录
    public  boolean isRepeatUser(String user) throws IOException, SQLException {
        String sql = "SELECT name FROM online_user WHERE name = ? LIMIT 1";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,user);
        resultSet = preparedStatement.executeQuery();
        return resultSet.next();
    }

    //写入登录用户

    public boolean writeUser(String user) throws SQLException {
        String sql = "INSERT INTO online_user (name) VALUE (?)";

        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,user);

        int i = preparedStatement.executeUpdate();
        if(i == 1){
            return true;
        }else {
            return false;
        }
    }
    //用户退出则删除用户
    public boolean removeOnlineUser(String user) throws SQLException {
        try {
            String sql = "DELETE FROM online_user WHERE name = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,user);
            int i = preparedStatement.executeUpdate();

            if (i > 0){
                return true;
            }else {
                return false;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //查询找回密钥
    public boolean isContainKey(String user,String Key) throws SQLException {
        String sql = "SELECT * FROM users WHERE username = ? AND secret_key = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,user);
        preparedStatement.setString(2,Key);
        return preparedStatement.executeQuery().next();
    }

    //查看该账号的密码并返回
    public String getPassword(String user) throws SQLException {
        String sql = "SELECT  password FROM users WHERE username = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,user);
        resultSet = preparedStatement.executeQuery();
        String pw = "";

        if (resultSet.next()){
            pw = resultSet.getString("password");
        }
        return pw;
    }

    //关闭数据库连接
    public void close(){
        try {
            if (!(resultSet == null)){
                resultSet.close();
            }
            if (!(preparedStatement == null)){
                preparedStatement.close();
            }
            if (!(connection == null)){
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    //查询酒店信息
    public ArrayList<String> FindHotel(String hotelName){
        ArrayList<String> arrayList = new ArrayList<>();
        return arrayList;
    }

    //查询车票信息，
    public ArrayList<String> FindTicket(String start ,String end){
        ArrayList<String> arrayList = new ArrayList<>();
        return arrayList;
    }

    //景点查询,按名字查询
    public ArrayList<Lvyou> FindSpot(String spotName) throws SQLException {
        ArrayList<Lvyou> lvyouList = new ArrayList<>();
        String sql = "SELECT * FROM lvyou WHERE arrange = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,spotName);
        ResultSet resultSet1 = preparedStatement1.executeQuery();
        while (resultSet1.next()){
            Lvyou lvyou = new Lvyou(
                    resultSet1.getInt(1),
                    resultSet1.getString(2),
                    resultSet1.getString(3),
                    resultSet1.getString(4),
                    resultSet1.getInt(5)
            );
            lvyouList.add(lvyou);

        }
        return lvyouList;
    }

    //景点查询按地址查询,然后返回所有查询的集合
    public ArrayList<Lvyou> FindSpot2(String dest) throws SQLException {
        ArrayList<Lvyou> lvyouArrayList = new ArrayList<>();
        String sql = "SELECT * FROM lvyou WHERE place LIKE ? ";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,"%" + dest +"%");
        ResultSet resultSet1 = preparedStatement1.executeQuery();
        while (resultSet1.next()){
            lvyouArrayList.add(new Lvyou(
                    resultSet1.getInt(1),
                    resultSet1.getString(2),
                    resultSet1.getString(3),
                    resultSet1.getString(4),
                    resultSet1.getInt(5)
            ));
        }

        return lvyouArrayList;
    }

    //按商品编号购买,
    public int  isBuySuccess(String id,String username) throws SQLException {
        String sql = "SELECT reminder,price FROM lvyou WHERE id = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,id);
        ResultSet resultSet1 = preparedStatement1.executeQuery();
        resultSet1.next();
        int anInt = resultSet1.getInt(1);
        String string = resultSet1.getString(2);
        if( anInt == 0 ){//表示商品数量够不够
            return 1;
        }else if (!isEnough(string,username)){//表示用户金额够不够
            return 2;
        }

        String sql2 = "UPDATE lvyou SET reminder = reminder - 1 WHERE id = ?";
        PreparedStatement preparedStatement2 = connection.prepareStatement(sql2);
        preparedStatement2.setString(1,id);
        preparedStatement2.executeUpdate();

        substractMoney(username,string);
        return 0;


    }
    //转入景点id和用户名，然后把该商品和用户名写入销售表
    public boolean writeSale(String id,String username) throws SQLException {
        String sql = "SELECT  arrange,price,id FROM lvyou WHERE id = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,id);
        ResultSet resultSet1 = preparedStatement1.executeQuery();
        resultSet1.next();
        String string = resultSet1.getString(1);
        String string3 = resultSet1.getString(2);
        String string4 = resultSet1.getString(3);
        int result = 0;
        String isNUll = isContainUserAndGoodsInSale(username,"spot");
        //1用户存在商品不存在   2用户存在商，商品存在，3不存在返回0
        if (isNUll.split("-")[0].equals("2")){
            String sql1 = "UPDATE sale SET spot = ? WHERE username = ?";
            PreparedStatement preparedStatement2 = connection.prepareStatement(sql1);
            preparedStatement2.setString(1,  isNUll.split("-")[1] + "/" + string);
            preparedStatement2.setString(2,username);
            result  = preparedStatement2.executeUpdate();


        }else if(isNUll.equals("0")){
            String sql1 = "INSERT INTO sale (username,spot) VALUES(?,?)";
            PreparedStatement preparedStatement5 = connection.prepareStatement(sql1);
            preparedStatement5.setString(1,username);
            preparedStatement5.setString(2,string);
            result =  preparedStatement5.executeUpdate();

        }else {
            String sql1 = "UPDATE sale SET spot = ? WHERE username = ?";
            PreparedStatement preparedStatement2 = connection.prepareStatement(sql1);
            preparedStatement2.setString(1,  string);
            preparedStatement2.setString(2,username);
            result  = preparedStatement2.executeUpdate();
        }

        return result > 0;

    }

    //购买景点票后写入数据库,这个按照景点名
    public boolean buySpotTicket(Lvyou buy, String username) throws SQLException {
        if (!isEnough(buy.getPrice(), username)){
            return false;
        }
        int result = 0;
        String isNUll = isContainUserAndGoodsInSale(username,"spot");
        //1用户存在商品不存在   2用户存在商，商品存在，3不存在返回0
        if (isNUll.split("-")[0].equals("2")){
            String sql = "UPDATE sale SET spot = ? WHERE username = ?";
            PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
            preparedStatement1.setString(1,  isNUll.split("-")[1] + "/" +buy.getArrange());
            preparedStatement1.setString(2,username);
            result = preparedStatement1.executeUpdate();

        }else if(isNUll.equals("0")){
            String sql1 = "INSERT INTO sale (username,spot) VALUES(?,?)";
            PreparedStatement preparedStatement1 = connection.prepareStatement(sql1);
            preparedStatement1.setString(1,username);
            preparedStatement1.setString(2,buy.getArrange());
            result = preparedStatement1.executeUpdate();

        }else {
            String sql = "UPDATE sale SET spot = ? WHERE username = ?";
            PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
            preparedStatement1.setString(1,  buy.getArrange());
            preparedStatement1.setString(2,username);
            result = preparedStatement1.executeUpdate();
        }

        if (result > 0){
            substractMoney(username,buy.getPrice());
            substractGoods(String.valueOf(buy.getId()),"lvyou");
        }
        return true;
    }

    //购买后商品数量减一
    public boolean substractGoods(String goodsId,String tableName) throws SQLException {
        String sql = "UPDATE "+ tableName +" SET reminder = reminder - 1 WHERE id = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);

        preparedStatement1.setString(1,goodsId);
        int i = preparedStatement1.executeUpdate();
        return i > 0;
    }

    //判断数据中的sale表是否已经有了该用户的记录

    //1用户存在商品不存在   2用户存在商，商品存在，3不存在返回0
    public String isContainUserAndGoodsInSale(String username,String kind) throws SQLException {
        String sql1 = "SELECT * FROM sale WHERE username = ? ";
        PreparedStatement preparedStatement11 = connection.prepareStatement(sql1);
        preparedStatement11.setString(1,username);
        ResultSet resultSet11 = preparedStatement11.executeQuery();
        String sql100 =  resultSet11.next()?"1":"0";
        String a =kind;
        String sql = "SELECT " + kind + " FROM sale WHERE username = ? AND "+ a +" is not Null";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,username);

        ResultSet resultSet1 = preparedStatement1.executeQuery();
        String sql0 = resultSet1.next()?"2" + "-" +resultSet1.getString(1):"0";

        if (sql0.split("-")[0].equals("2")){

            return sql0;
        } else if (sql100.equals("1")) {

            return sql100;
        }else {

            return "0";
        }


    }

    //判断用户金额是否足够
    public boolean isEnough(String price,String username) throws SQLException {
        String sql = "SELECT money FROM users WHERE username = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,username);
        ResultSet resultSet1 = preparedStatement1.executeQuery();
        resultSet1.next();
        String userMoney = resultSet1.getString(1);


        return Double.parseDouble(price) < Integer.parseInt(userMoney);//返回ture则为足够
    }

    //对用户的钱进行减法
    public boolean substractMoney(String username, String price) throws SQLException {
        String sql2 = "UPDATE users SET money = money - ? WHERE username = ?";
        PreparedStatement preparedStatement2 = connection.prepareStatement(sql2);
        preparedStatement2.setString(1,price);
        preparedStatement2.setString(2,username);
        return preparedStatement2.executeUpdate() > 0;
    }

    //按照输入的目的地查询车票,返回一个查询的结果集合
    public ArrayList<Train> findDestTicket(String dest) throws SQLException {

        ArrayList<Train> trainArrayList = new ArrayList<>();
        String sql = "SELECT * FROM train WHERE place = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,dest);
        ResultSet resultSet1 = preparedStatement1.executeQuery();

        while (resultSet1.next()){
            trainArrayList.add(new Train(
                    resultSet1.getInt(1),
                    resultSet1.getString(2),
                    resultSet1.getString(3),
                    resultSet1.getString(4),
                    resultSet1.getString(5),
                    resultSet1.getInt(6)
            ));
        }

        return trainArrayList;
    }

    //订购写入车票并写入
    //返回1表示钱不足，返回2表示商品不足，返回0表示购买成功
    public int  orderTicket(String ticketId,String username) throws SQLException {
        //根据id查询到该票的三种状态
        String sql = "SELECT ticket,price,location,reminder FROM train WHERE id =?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,ticketId);
        ResultSet resultSet1 = preparedStatement1.executeQuery();
        resultSet1.next();
        Train train = new Train(
                null,
                null,
                resultSet1.getString(1),
                resultSet1.getString(2),
                resultSet1.getString(3),
                resultSet1.getInt(4)
        );

        //查询商品是否充足
        if (train.getReminder() == 0){
            return 2;
        }
        //查询钱是否充足
        if(!isEnough(train.getPrice(),username)){
            return 1;
        }

        int result = 0;
        //条件都满足则1，商品写入销售表
        String isNUll = isContainUserAndGoodsInSale(username,"ticket");
        System.out.println(isNUll);
        if (isNUll.split("-")[0].equals("2")){

            //销售表中有该用户
            String link = isNUll.split("-")[1] + "/" + train.getTicket() + "-" + train.getLocation();
            String sql1 = "UPDATE sale SET ticket = ? WHERE username = ?";
            PreparedStatement preparedStatement2 = connection.prepareStatement(sql1);
            preparedStatement2.setString(1,link);
            preparedStatement2.setString(2,username);
            result = preparedStatement2.executeUpdate();
        }else if(isNUll.equals("0")){
            //销售表中没有该用户
            String sql2 = "INSERT INTO sale (username,ticket) values (?,?)";
            PreparedStatement preparedStatement2 = connection.prepareStatement(sql2);
            preparedStatement2.setString(2,train.getTicket() + "-" + train.getLocation());
            preparedStatement2.setString(1,username);
            result = preparedStatement2.executeUpdate();

        }else {
            String link = train.getTicket() + "-" + train.getLocation();
            String sql1 = "UPDATE sale SET ticket = ? WHERE username = ?";
            PreparedStatement preparedStatement2 = connection.prepareStatement(sql1);
            preparedStatement2.setString(1,link);
            preparedStatement2.setString(2,username);
            result = preparedStatement2.executeUpdate();
        }

        if (result > 0){
            substractMoney(username,train.getPrice());
            substractGoods(ticketId,"train");
        }
        return result > 0 ? 0:3;

    }


    //根据开始地和目的地查询票
    public ArrayList<Train> findStartToEndTicket(String start, String end) throws SQLException {
        ArrayList<Train> trainArrayList = new ArrayList<>();
        String  sql = "select * from train where location like ? and location like ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,"%" + start + "%");
        preparedStatement1.setString(2,"%" + end + "%");
        ResultSet resultSet1 = preparedStatement1.executeQuery();

        while (resultSet1.next()){
            trainArrayList.add(new Train(
                    resultSet1.getInt(1),
                    resultSet1.getString(2),
                    resultSet1.getString(3),
                    resultSet1.getString(4),
                    resultSet1.getString(5),
                    resultSet1.getInt(6)
            ));
        }

        return trainArrayList;

    }

    //按照酒店名称查询
    public ArrayList<Hotel> findHotelName(String hotelName) throws SQLException {

        ArrayList<Hotel> trainArrayList = new ArrayList<>();
        String sql = "SELECT * FROM hotel WHERE username = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,hotelName);
        ResultSet resultSet1 = preparedStatement1.executeQuery();

        while (resultSet1.next()){
            trainArrayList.add(new Hotel(
                    resultSet1.getInt(1),
                    resultSet1.getString(2),
                    resultSet1.getString(3),
                    resultSet1.getString(4),
                    resultSet1.getInt(5)
            ));
        }

        return trainArrayList;
    }

    //订购写入酒店并写入
    //返回1表示钱不足，返回2表示商品不足，返回0表示购买成功
    public int  orderHotel(String hotelId,String username) throws SQLException {
        //根据id查询到该票的三种状态
        String sql = "SELECT * FROM hotel WHERE id =?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,hotelId);
        ResultSet resultSet1 = preparedStatement1.executeQuery();
        resultSet1.next();
        Hotel hotel = new Hotel(
                resultSet1.getInt(1),
                resultSet1.getString(2),
                resultSet1.getString(3),
                resultSet1.getString(4),
                resultSet1.getInt(5)
        );

        //查询商品是否充足
        if (hotel.getReminder() == 0){
            return 2;
        }
        //查询钱是否充足
        if(!isEnough(hotel.getPrice(),username)){
            return 1;
        }

        int result = 0;
        //条件都满足则1，商品写入销售表
        String isNUll = isContainUserAndGoodsInSale(username,"hotel");

        if (isNUll.split("-")[0].equals("2")){

            //销售表中有该用户
            String link = isNUll.split("-")[1] + "/" + hotel.getUsername();
            System.out.println(link);
            String sql1 = "UPDATE sale SET hotel = ? WHERE username = ?";
            PreparedStatement preparedStatement2 = connection.prepareStatement(sql1);
            preparedStatement2.setString(1,link);
            preparedStatement2.setString(2,username);
            result = preparedStatement2.executeUpdate();
        }else if(isNUll.equals("0")) {
            //销售表中没有该用户
            String sql2 = "INSERT INTO sale (username,hotel) values (?,?)";
            PreparedStatement preparedStatement2 = connection.prepareStatement(sql2);
            preparedStatement2.setString(2,hotel.getUsername());
            preparedStatement2.setString(1,username);
            result = preparedStatement2.executeUpdate();

        }else {
            String link = hotel.getUsername();
            String sql1 = "UPDATE sale SET hotel = ? WHERE username = ?";
            PreparedStatement preparedStatement2 = connection.prepareStatement(sql1);
            preparedStatement2.setString(1,link);
            preparedStatement2.setString(2,username);
            result = preparedStatement2.executeUpdate();
        }

        if (result > 0){
            substractMoney(username,hotel.getPrice());
            substractGoods(hotelId,"hotel");
        }
        return result > 0 ? 0:3;

    }

    //酒店所在地查询

    public ArrayList<Hotel> findHotelDest(String hotelDest) throws SQLException {

        ArrayList<Hotel> trainArrayList = new ArrayList<>();
        String sql = "SELECT * FROM hotel WHERE place = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,hotelDest);
        ResultSet resultSet1 = preparedStatement1.executeQuery();

        while (resultSet1.next()){
            trainArrayList.add(new Hotel(
                    resultSet1.getInt(1),
                    resultSet1.getString(2),
                    resultSet1.getString(3),
                    resultSet1.getString(4),
                    resultSet1.getInt(5)
            ));
        }

        return trainArrayList;
    }

    //获取用户的个人信息
    public User getSelfInfo(String user) throws SQLException {

        String sql = " select * from users where username = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,user);
        ResultSet resultSet1 = preparedStatement1.executeQuery();
        resultSet1.next();
         User user1 = new User(
                resultSet1.getInt(1),
                resultSet1.getString(2),
                resultSet1.getString(6),
                resultSet1.getString(7),
                resultSet1.getString(3),
                resultSet1.getString(4),
                resultSet1.getString(5)
        );
         return user1;
    }

    //修改用户密码
    public boolean modifyPsw(String username,String newPwd) throws SQLException {
        String sql = "update users set password = ? where username = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,newPwd);
        preparedStatement1.setString(2,username);
        return preparedStatement1.executeUpdate() > 0;
    }
    //修改用户找回凭证
    public boolean modifyKey(String username, String newKey) throws SQLException {
        String sql = "update users set secret_key = ? where username = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,newKey);
        preparedStatement1.setString(2,username);
        return preparedStatement1.executeUpdate() > 0;
    }
    //修改年龄
    public boolean modifyAge(String username, String newAge) throws SQLException {
        String sql = "update users set age = ? where username = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,newAge);
        preparedStatement1.setString(2,username);
        return preparedStatement1.executeUpdate() > 0;
    }
    //修改性别
    public boolean modifyGnder(String username, String newGender) throws SQLException {
        String sql = "update users set gender = ? where username = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,newGender);
        preparedStatement1.setString(2,username);
        return preparedStatement1.executeUpdate() > 0;
    }

    //查看此用户的购票信息
    public Sale findOrderInfo(String username) throws SQLException {
        String sql = "select * from sale where username =?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,username);
        ResultSet resultSet1 = preparedStatement1.executeQuery();
        boolean next = resultSet1.next();
        if(next){
            return new Sale(
                    resultSet1.getString(1),
                    resultSet1.getString(2),
                    resultSet1.getString(3),
                    resultSet1.getString(4)
            );
        }else {
            return null;
        }

    }
    //账号充值
    public boolean userRecharge(String username, String money) throws SQLException {
        String sql = "update users set money = money + ? where username = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,money);
        preparedStatement1.setString(2,username);
        return  preparedStatement1.executeUpdate() > 0;

    }

    //清空订购信息
    public boolean cancelTotalOrder(String username) throws SQLException {
        String sql = "delete from sale where username = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,username);
        return preparedStatement1.executeUpdate() > 0;
    }

    public <T> ArrayList getAllInfo(T info) throws SQLException, InstantiationException, IllegalAccessException {

        Class aClass = info.getClass();
        String[] split = aClass.getName().split("\\.");
        String s = split[split.length - 1];
        String sql = "select *from " + s;
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        ResultSet resultSet1 = preparedStatement1.executeQuery();

        if(s.equals("Train")){
            ArrayList<Train> trainArrayList = new ArrayList<>();
            while (resultSet1.next()){
                trainArrayList.add(new Train(
                        resultSet1.getInt(1),
                        resultSet1.getString(2),
                        resultSet1.getString(3),
                        resultSet1.getString(4),
                        resultSet1.getString(5),
                        resultSet1.getInt(6)
                ));
            }
            return trainArrayList;
        }else if (s.equals("Hotle")){
            ArrayList<Hotel> hotelArrayList = new ArrayList<>();
            while (resultSet1.next()){
                hotelArrayList.add(new Hotel(
                        resultSet1.getInt(1),
                        resultSet1.getString(2),
                        resultSet1.getString(3),
                        resultSet1.getString(4),
                        resultSet1.getInt(5)
                ));
            }
            return hotelArrayList;
        }else {
            ArrayList<Lvyou> lvyouArrayList = new ArrayList<>();
            while (resultSet1.next()){
                lvyouArrayList.add(new Lvyou(
                        resultSet1.getInt(1),
                        resultSet1.getString(2),
                        resultSet1.getString(3),
                        resultSet1.getString(4),
                        resultSet1.getInt(5)
                ));
            }
            return lvyouArrayList;
        }


    }

    //修改价格
    public <T> boolean modifyPrice(T info,String num,String stuff,String id) throws SQLException {
        String[] split = info.getClass().getName().split("\\.");
        String s = split[split.length - 1];

        if (stuff.equals("money")){
            String sql = "update " + s + " set price =? where id =?";
            PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
            preparedStatement1.setString(1,num);
            preparedStatement1.setString(2,id);
            int i = preparedStatement1.executeUpdate();
            return i > 0;
        }else {
            String sql = "update " + s + " set reminder = ? where id= ?"  ;
            PreparedStatement preparedStatement2 = connection.prepareStatement(sql);
            preparedStatement2.setString(1,num);
            preparedStatement2.setString(2,id);
            int i = preparedStatement2.executeUpdate();
            return i > 0;
        }

    }

    //下架商品
    public <T> boolean removeGoods(T info, String id) throws SQLException {
        String[] split = info.getClass().getName().split("\\.");
        String s = split[split.length - 1];
        String sql = "delete from " + s + " where id = ? ";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,id);
        int i = preparedStatement1.executeUpdate();
        return i > 0;
    }
//得到所有用户名
    public ArrayList<User> getAllUser() throws SQLException {
        ArrayList<User> list = new ArrayList<>();
        String sql = "select * from users;";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        ResultSet resultSet1 = preparedStatement1.executeQuery();
        while (resultSet1.next()){
            list.add(new User(
                    resultSet1.getInt(1),
                    resultSet1.getString(2),
                    resultSet1.getString(3),
                    resultSet1.getString(4),
                    resultSet1.getString(5),
                    resultSet1.getString(6),
                    resultSet1.getString(7)
            ));
        }
        return list;
    }

    //删除用户
    public boolean deleteUser(String dele) throws SQLException {
        String sql = "delete from users where id = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,dele);
        int i = preparedStatement1.executeUpdate();
        return i > 0;
    }


    //修改管理员密码
    public void modifyAdminPsw(String psw) throws SQLException {
        String sql = "update admin set password = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.executeUpdate();
    }

    //查询该用户所有订购信息
    public ArrayList<Sale> getALLSale(String username) throws SQLException {
        String sql = " select * from sale where username = ?";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql);
        preparedStatement1.setString(1,username);
        ResultSet resultSet1 = preparedStatement1.executeQuery();
        ArrayList<Sale> saleArrayList = new ArrayList<>();
        while (resultSet1.next()){
            saleArrayList.add(new Sale(
                    resultSet1.getString(1),
                    resultSet1.getString(2),
                    resultSet1.getString(3),
                    resultSet1.getString(4)
            ));
        }
        return saleArrayList;
    }
}
